package dao;

import Jdbc.Employee;
import sun.management.counter.Units;
import until.JdbcUntil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EmployeeDao {
	public int add(Employee employee) throws SQLException {
		Connection connection=JdbcUntil.getConn();
		String sql="INSERT INTO emp(ename,job,hiredate,sal,deptno) VALUES (?,?,?,?,?);";
		return JdbcUntil.dml(sql,employee.getEname(),employee.getJob(),
				employee.getHiredate(),employee.getSal(),employee.getDeptno());
	}
	public int update(Employee employee) throws SQLException {
		Connection connection=JdbcUntil.getConn();
		String sql="update emp set ename=?,job=?,hiredate=?,sal=?,deptno=? where empno=?";
		PreparedStatement pstm=connection.prepareStatement(sql);
		pstm.setObject(1,employee.getEname());
		pstm.setObject(2,employee.getJob());
		pstm.setObject(3,employee.getHiredate());
		pstm.setObject(4,employee.getSal());
		pstm.setObject(5,employee.getDeptno());
		pstm.setObject(6,employee.getEmpno());
		return pstm.executeUpdate();
	}
	public Employee getById(Integer empno) throws SQLException {
		Connection connection=JdbcUntil.getConn();
		String sql="select empno,ename,job,hiredate,sal,deptno from emp where empno=?";
		PreparedStatement pstm=connection.prepareStatement(sql);
		pstm.setObject(1,empno);
		ResultSet resultSet=pstm.executeQuery();
		while(resultSet.next()) {
			String ename=resultSet.getString("ename");
			String job=resultSet.getString("job");
			Date hiredate=resultSet.getDate("hiredate");
			Double sal=resultSet.getDouble("sal");
			Integer deptno =resultSet.getInt("deptno");
			return new Employee(empno,ename,job,hiredate,sal,deptno);
		}
		System.out.println("没有找到这个员工");
		return null;
	}
	public List<Employee> getAll() throws  SQLException {
		List<Employee> list=new ArrayList<>();
		Connection connection=JdbcUntil.getConn();
		String sql="select empno,ename,job,hiredate,sal,deptno from emp";
		PreparedStatement pstm=connection.prepareStatement(sql);
		ResultSet resultSet=pstm.executeQuery();
		while(resultSet.next()) {
			Integer empno=resultSet.getInt("empno");
			String ename=resultSet.getString("ename");
			String job=resultSet.getString("job");
			Date hiredate=resultSet.getDate("hiredate");
			Double sal=resultSet.getDouble("sal");
			Integer deptno =resultSet.getInt("deptno");
			Employee emp= new Employee(empno,ename,job,hiredate,sal,deptno);
			list.add(emp);
		}
		return list;
	}
	public List<Employee> getByEname(String enameCondition) throws Exception{
		String sql = "select empno,ename,job,hiredate,sal,deptno from emp where ename like CONCAT('%',?,'%')";
		return JdbcUntil.dqlMany(sql,Employee.class,enameCondition);
	}
}
